﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.SqlClient;
using NTTDATA.BEENChina.ResultCertification.Result;
using Microsoft.ApplicationBlocks.Data;

namespace NTTDATA.BEENChina.ResultCertification.DataStore
{
	public class DataSource:IDataSource
	{

		private   string ConnectionString
		{
			get
			{
				return System.Configuration.ConfigurationManager.AppSettings["AutoBackupDB"];
			}
		}

		public IEnumerable<string> GetTableNames(string prefixName,string servercode=null)
		{
			//sydyma
			//最新的store表名的格式  'Store_sydy%'
			string sql = null;
			

			if (!String.IsNullOrEmpty(servercode))
			{
				sql = "SELECT [name] FROM  SYSOBJECTS WITH (NOLOCK)  WHERE [name] LIKE  '{0}_{1}' ORDER BY [name]";
				sql = string.Format(sql, prefixName,servercode);

			}
			else
			{
				sql ="SELECT [name] FROM  SYSOBJECTS WITH (NOLOCK)  WHERE [name] LIKE  '{0}_sydyma%' ORDER BY [name]";
				sql = string.Format(sql, prefixName);
			}
		
			if (ConnectionString == null)
			{
				return null;
			}

			List<string> tableNames = null;

			try
			{ 
				using (SqlDataReader reader = SqlHelper.ExecuteReader(ConnectionString, System.Data.CommandType.Text, sql))
				{
					while (reader.Read())
					{
						string name = reader.GetString(0);

						if (!String.IsNullOrEmpty(name))
						{
							if (tableNames == null)
								tableNames = new List<string>();

							tableNames.Add(name);
						}
					}

					reader.Close();
				}
			}
			catch (Exception ex)
			{
				Log.Console.Error("DataSource.GetStoreTableNames", ex);
			}


			return tableNames;
		}

		/// <summary>
		/// </summary>
		/// <param name="dateFrom"></param>
		/// <param name="dateTo"></param>
		/// <param name="distriwkTableName"></param>
		/// <returns></returns>
		public IEnumerable<OrderData> GetCompleteOrdes(DateTime dateFrom, DateTime dateTo, string distriwkTableName)
		{
			//distriwkTableName = 'distriwk_sydyma0001'
			string serverCode = distriwkTableName.Replace("distriwk_","");
		    string sql =
		        " SELECT [f_distriwk_receipt_number] [OrderID] ,[OridReceiveDate] " +
		        " FROM  {0}  WITH (NOLOCK) " +
		        " WHERE OridReceiveDate BETWEEN  @dateFrom AND @dateTo  ";
                //" AND [f_distriwk_receipt_number] NOT LIKE 'ns%'  ";
			sql = string.Format(sql,distriwkTableName);

			SqlParameter[] sqlParameters = 
			{
				new SqlParameter("@dateFrom", dateFrom),
				new SqlParameter("@dateTo", dateTo)
			};

			List<OrderData> orderDatas = new List<OrderData>();
			try
			 {
				using (SqlDataReader reader = SqlHelper.ExecuteReader(ConnectionString, System.Data.CommandType.Text, sql,sqlParameters))
				{
					while (reader.Read())
					{

						orderDatas.Add(
							new OrderData()
								{
									OrderId = reader["OrderID"].ToString(), 
									ServerCode = serverCode ,
                                    OridReceiveDate = reader.GetDateTime(1).ToString("yyyy-MM-dd HH:mm:ss")
                                    //reader["OridReceiveDate"].ToString()
								}
						);
					}

					reader.Close();
				}
			}
			catch (Exception ex)
			{
				Log.Console.Error("DataSource.GetCompleteOrdes", ex);
			}

			return orderDatas;

		}

		/// <summary>
		/// 在调用前 已经确认了categoryid
		/// 保证categoryid 都存在
		/// 
		/// 测试过程中发现 此方法 执行速度慢 超时
		/// </summary>
		/// <param name="storeTableName"></param>
		/// <param name="orderId"></param>
		/// <param name="categoryIds"></param>
		/// <returns></returns>
		public List<ChildImage> GetChildImageObjects(string storeTableName, string orderId, List<string> categoryIds)
		{
			List<ChildImage> imagesChilds = new List<ChildImage>();


			List<string> categorys = categoryIds.Select(t => string.Format("'{0}'", t)).ToList();

			string categoryFileds = string.Join(",", categorys);
			//int effactRow = GetChildImageObjectExist(storeTableName, orderId, categoryId);

			//if (effactRow == 0)
			//	return imagesChilds; 


			string sql = "SELECT    " +
						 "      A.f_store_receipt_number [OrderID],A.f_store_child_pic_id[ImageID],  C.f_field_form_id [FormID] ," +
						 "      A.f_store_customer_id [CustomerID] ,A.f_store_field_id [FieldID], C.f_field_name [FieldName] , " +
						 "      C.f_field_category_id  [CategoryID], " +
                         "      C.EntryRule [EntryRule],  "  +
						 "      A.AEntryResult [AResult],A.AEntryCommentID [AEntryCommentID],  " +
						 "      A.BEntryResult [BResult],A.BEntryCommentID [BEntryCommentID],  " +
						 "      A.CompareEntryResult [CResult],A.CompareCommentID [CEntryCommentID],  " +
						 "      A.DEntryEntryResult [DResult],A.DEntryCommentID [DEntryCommentID],   " +
						 "      A.DCheckResult [EResult],   " +
						 "      A.f_store_entry_result [FinalResult],A.f_store_sticky_id  [FinalEntryCommentID],  " +

						 "      A.jobID [ABjobId],  A.CompareJobID  [CjobId] , A.DEntryJobID [DjobId]   ,   A.DCheckJobID [EjobId]  " +

						" FROM {0} A WITH (NOLOCK) " +
							" INNER JOIN t_im_field_mas  C  WITH (NOLOCK) ON " +
							" C.f_field_customer_id = A.f_store_customer_id AND  " +
							" C.f_field_field_id =A.f_store_field_id  " +
					" WHERE f_store_receipt_number = @orderId  AND   C.f_field_category_id  in( {1} )"; 



			// set tableName
			sql = string.Format(sql, storeTableName,categoryFileds);

			SqlParameter[] sqlParameters = 
			{ 
				new SqlParameter("@orderId", orderId)  
			};

			try
			{
				imagesChilds = GetValue(storeTableName, orderId, sql, sqlParameters);
			}
			catch (Exception ex)
			{
				Log.Console.Error(string.Format("DataSource.GetChildImageObjects - list. orid:{0}", orderId), ex);
			}
			return imagesChilds;
			 
		}

		public List<ChildImage> GetChildImageObjects(string storeTableName, string orderId, string categoryId)
		{
			List<ChildImage> imagesChilds = new List<ChildImage>();  
	 
			//int effactRow = GetChildImageObjectExist(storeTableName, orderId, categoryId);

			//if (effactRow == 0)
			//	return imagesChilds; 

			string sql = "SELECT    " +
						 "      A.f_store_receipt_number [OrderID],A.f_store_child_pic_id[ImageID],  C.f_field_form_id [FormID] ," + 
						 "      A.f_store_customer_id [CustomerID] ,A.f_store_field_id [FieldID], C.f_field_name [FieldName] , " +
						 "      C.f_field_category_id  [CategoryID], " +
                         "      C.EntryRule [EntryRule],  " +
						 "      A.AEntryResult [AResult],A.AEntryCommentID [AEntryCommentID],  " +
						 "      A.BEntryResult [BResult],A.BEntryCommentID [BEntryCommentID],  " +
						 "      A.CompareEntryResult [CResult],A.CompareCommentID [CEntryCommentID],  " +
						 "      A.DEntryEntryResult [DResult],A.DEntryCommentID [DEntryCommentID],   " +
						 "      A.DCheckResult [EResult],   " +
						 "      A.f_store_entry_result [FinalResult],A.f_store_sticky_id  [FinalEntryCommentID],  " +

						 "      A.jobID [ABjobId],  A.CompareJobID  [CjobId] , A.DEntryJobID [DjobId]   ,   A.DCheckJobID [EjobId]  " +

						" FROM {0} A WITH (NOLOCK) " +
							" INNER JOIN t_im_field_mas  C  WITH (NOLOCK) ON " +
							" C.f_field_customer_id = A.f_store_customer_id AND  " +
							" C.f_field_field_id =A.f_store_field_id  " +

			" WHERE f_store_receipt_number = @orderId  AND C.f_field_category_id = @categoryId";



			// set tableName
			sql = string.Format(sql, storeTableName );

			SqlParameter[] sqlParameters = 
			{ 
				new SqlParameter("@orderId", orderId),
				 new SqlParameter("@categoryId", categoryId),
			};

			try
			{
				imagesChilds = GetValue(storeTableName, orderId,  sql, sqlParameters); 
			 }
			catch (Exception ex)
			{
				Log.Console.Error(string.Format("DataSource.GetChildImageObjects - single. orid:{0}", orderId), ex);
			}
			return imagesChilds;
		}

		private  List<ChildImage> GetValue(string storeTableName, string orderId, string sql, SqlParameter[] sqlParameters)
		{

			List<ChildImage> imagesChilds = null;
			
				using (
					SqlDataReader reader = SqlHelper.ExecuteReader(ConnectionString, System.Data.CommandType.Text, sql,
																   sqlParameters))
				{
					while (reader.Read())
					{
						ChildImage child = new ChildImage()
											   {
												   OrderId = reader["OrderID"].ToString(),
												   ImageId = reader["ImageID"].ToString(),
												   FormId = reader["FormID"].ToString(),
												   //FormName = reader["FormName"].ToString(),
												   CustomerId = reader["CustomerID"].ToString(),
												   FieldId = reader["FieldID"].ToString(),
												   FieldName = reader["FieldName"].ToString(),
												   CategoryId = reader["CategoryID"].ToString(),
												   //CategoryName = reader["CategoryName"].ToString(),
												   StoreTableName = storeTableName,
                                                   EntryRule = reader["EntryRule"].ToString()
											   };

						string abjobId = reader["ABjobId"].ToString();
						string cjobId = reader["CjobId"].ToString();
						string djobId = reader["DjobId"].ToString();
						string ejobId = reader["EjobId"].ToString();


						if (!String.IsNullOrEmpty(abjobId))
						{
							child.OperatorEntryResults.Add(new OpEntryResult
															   {
																   Rank = EnumRank.A,
																   EntryResult = reader["AResult"].ToString(),
																   EntryCommon = reader["AEntryCommentID"].ToString(),
																   JobId = abjobId
															   });

							child.OperatorEntryResults.Add(new OpEntryResult
															   {
																   Rank = EnumRank.B,
																   EntryResult = reader["BResult"].ToString(),
																   EntryCommon = reader["BEntryCommentID"].ToString(),
																   JobId = abjobId
															   });
						}

						if (!String.IsNullOrEmpty(cjobId))
						{
							child.OperatorEntryResults.Add(new OpEntryResult
															   {
																   Rank = EnumRank.C,
																   EntryResult = reader["CResult"].ToString(),
																   EntryCommon = reader["CEntryCommentID"].ToString(),
																   JobId = cjobId
															   });
						}

						if (!String.IsNullOrEmpty(djobId))
						{
							child.OperatorEntryResults.Add(new OpEntryResult
															   {
																   Rank = EnumRank.D,
																   EntryResult = reader["DResult"].ToString(),
																   EntryCommon = reader["DEntryCommentID"].ToString(),
																   JobId = djobId
															   });
						}

						if (!String.IsNullOrEmpty(ejobId))
						{
							child.OperatorEntryResults.Add(new OpEntryResult
															   {
																   Rank = EnumRank.E,
																   //E的付签值等于E的结果
																   EntryCommon = reader["EResult"].ToString(),
																   EntryResult = reader["EResult"].ToString(),
																   //E0 :0  E1:1  E2:2 
																   JobId = ejobId
															   });
						}

						child.OperatorEntryResults.Add(new OpEntryResult
														   {
															   Rank = EnumRank.Final,
															   EntryResult = reader["FinalResult"].ToString(),
															   EntryCommon = reader["FinalEntryCommentID"].ToString()
														   });


						if (imagesChilds == null)
							imagesChilds = new List<ChildImage>();
							
							imagesChilds.Add(child);
					}

					reader.Close();
				}

			return imagesChilds;
		}

		public List<string> GetCategoryIdsFromChildImage(string storeTableName, string orderId)
		{
			List<string> categorys = null;

			string sql = " SELECT  DISTINCT C.f_field_category_id  [CategoryId] " +
						 " FROM {0} A WITH (NOLOCK)  " +
						 " INNER JOIN t_im_field_mas  C  WITH (NOLOCK) ON " +
						 "          C.f_field_customer_id = A.f_store_customer_id AND  " +
						 "          C.f_field_field_id =A.f_store_field_id  " +
						 " WHERE f_store_receipt_number = @orderId   ";

			sql = string.Format(sql, storeTableName);

			SqlParameter[] sqlParameters = 
			{ 
				new SqlParameter("@orderId", orderId)
			};

			try
			{
				using (SqlDataReader reader = SqlHelper.ExecuteReader(ConnectionString, System.Data.CommandType.Text, sql, sqlParameters))
				{
					while (reader.Read())
					{
						if (categorys == null)
							categorys = new List<string>();

						categorys.Add(reader.GetString(0));
					}

					reader.Close();
					
				}

			}
			catch (Exception ex)
			{

				Log.Console.Error("DataSource.GetCategoryIdsFromChildImage", ex);
			}

			return categorys;
		}


		private int GetChildImageObjectExist(string storeTableName, string orderId, string categoryId)
		{
			int effactRow = 0;
			string sql = " SELECT  COUNT(*)  "+
						 " FROM {0} A WITH (NOLOCK)  "+
						 " INNER JOIN t_im_field_mas  C  WITH (NOLOCK) ON "+
						 "          C.f_field_customer_id = A.f_store_customer_id AND  "+
						 "          C.f_field_field_id =A.f_store_field_id  "+
						 " WHERE f_store_receipt_number = @orderId  AND C.f_field_category_id = @categoryId" ;

			// set tableName
			sql = string.Format(sql, storeTableName);

			SqlParameter[] sqlParameters = 
			{ 
				new SqlParameter("@orderId", orderId),
				new SqlParameter("@categoryId", categoryId),
			};

			try
			{
				effactRow = int.Parse(SqlHelper.ExecuteScalar(ConnectionString, System.Data.CommandType.Text, sql, sqlParameters).ToString());

			}
			catch (Exception ex)
			{

				Log.Console.Error("DataSource.GetChildImageObjectExist", ex);
			}

			return effactRow;
		}

		/// <summary>
		/// 获得AB operatorid
		/// </summary>
		/// <param name="tableName"></param>
		/// <param name="custormerId"></param>
		/// <param name="receiptNumber"></param>
		/// <param name="jobId"></param>
		/// <param name="autonumber"></param>
		/// <returns></returns>
		private string GetABopId(string tableName, string custormerId, string receiptNumber, string jobId,string autonumber)
		{
			if (String.IsNullOrEmpty(jobId))return string.Empty;


			// abjobmana_sydyma0053
			string sql = " SELECT  f_jobmana_operator_no [opID] FROM   {0} WITH (NOLOCK) " +
						 " WHERE    f_jobmana_customer_id = @customerId AND " +
						 "          f_jobmana_receipt_number = @receiptnumber AND" +
						 "          f_jobmana_job_id = @jobId AND" +
						 "          f_jobmana_autonumber = @autonumber ";

			// set tableName
			sql = string.Format(sql, tableName);

			SqlParameter[] sqlParameters = 
			{ 
				new SqlParameter("@customerId", custormerId),
				new SqlParameter("@receiptnumber", receiptNumber),
				new SqlParameter("@jobId", jobId),
				new SqlParameter("@autonumber", autonumber),

			};
			string opid = string.Empty;
			try
			{
				  opid =
					SqlHelper.ExecuteScalar(ConnectionString, System.Data.CommandType.Text, sql, sqlParameters).
						ToString();
			}
			catch (Exception ex)
			{
				Log.Console.Error("DataSource.GetABopId", ex);
			}

			return opid;
		}

		/// <summary>
		/// 获得C operatorid
		/// </summary>
		/// <param name="tableName"></param>
		/// <param name="custormerId"></param>
		/// <param name="receiptNumber"></param>
		/// <param name="jobId"></param>
		/// <returns></returns>
		private string GetCopId(string tableName, string custormerId, string receiptNumber, string jobId)
		{
			if (String.IsNullOrEmpty(jobId)) return string.Empty;

			// cjobmana_sydyma0053
			string sql = " SELECT  OperatorID [opID] FROM   {0} WITH (NOLOCK) " +
						 "  WHERE  CustomerID = @customerId AND " +
						 "    ReceiptNumber =  @receiptnumber AND" +
						 "    CompareJobID = @jobId  ";

			// set tableName
			sql = string.Format(sql, tableName);

			SqlParameter[] sqlParameters = 
			{ 
				new SqlParameter("@customerId", custormerId),
				new SqlParameter("@receiptnumber", receiptNumber),
				new SqlParameter("@jobId", jobId) 
			};

			 string opid = string.Empty;

			 try
			 {
				 opid =
					 SqlHelper.ExecuteScalar(ConnectionString, System.Data.CommandType.Text, sql, sqlParameters).
						 ToString();
			 }
			 catch (Exception ex)
			 {
				 Log.Console.Error("DataSource.GetCopId", ex);
			 }

			return opid;
		}

		/// <summary>
		/// 获得D operatorid
		/// </summary>
		/// <param name="tableName"></param>
		/// <param name="custormerId"></param>
		/// <param name="receiptNumber"></param>
		/// <param name="jobId"></param>
		/// <returns></returns>
		private string GetDopId(string tableName, string custormerId, string receiptNumber, string jobId)
		{
			if (String.IsNullOrEmpty(jobId)) return string.Empty;

			// cjobmana_sydyma0053
			string sql = " SELECT  OperatorID [opID] FROM   {0} WITH (NOLOCK) " +
						 "  WHERE  CustomerID = @customerId AND " +
						 "    ReceiptNumber =  @receiptnumber AND" +
						 "    DEntryJobID = @jobId  ";

			// set tableName
			sql = string.Format(sql, tableName);

			SqlParameter[] sqlParameters = 
			{ 
				new SqlParameter("@customerId", custormerId),
				new SqlParameter("@receiptnumber", receiptNumber),
				new SqlParameter("@jobId", jobId) 
			};

			  string opid = string.Empty;

			  try
			  {
				  opid =
					  SqlHelper.ExecuteScalar(ConnectionString, System.Data.CommandType.Text, sql, sqlParameters).
						  ToString();
			  }
			  catch (Exception ex)
			  {
				  Log.Console.Error("DataSource.GetDopId", ex);
			  }

			return opid;
		}

		/// <summary>
		/// 获得E operatorid
		/// E0 :3  E1:1  E2:2 
		/// </summary>
		/// <param name="tableName"></param>
		/// <param name="custormerId"></param>
		/// <param name="receiptNumber"></param>
		/// <param name="jobId"></param>
		/// <param name="eRankId"></param>
		/// <returns></returns>
		private string GetEopId(string tableName, string custormerId, string receiptNumber, string jobId)
		{
			if (String.IsNullOrEmpty(jobId)) return string.Empty;

			// cjobmana_sydyma0053
			string sql = " SELECT top 1  OperatorID [opID] FROM   {0} WITH (NOLOCK) " +
						 "  WHERE  CustomerID = @customerId AND " +
						 "    ReceiptNumber =  @receiptnumber AND" +
						 "    DCheckJobID = @jobId  " +
						 " ORDER BY ERankid ";

			// set tableName
			sql = string.Format(sql, tableName);

			SqlParameter[] sqlParameters = 
			{ 
				new SqlParameter("@customerId", custormerId),
				new SqlParameter("@receiptnumber", receiptNumber),
				new SqlParameter("@jobId", jobId)  
			};

			string opid = string.Empty;
			try
			{
			   opid = SqlHelper.ExecuteScalar(ConnectionString, System.Data.CommandType.Text, sql, sqlParameters).ToString();

			}
			catch (Exception ex)
			{

				Log.Console.Error("DataSource.GetEopId", ex);

			}


			return opid;
		}


		public ChildImage CompleteOpId( ChildImage childImage)
		{
			if (childImage == null)
				return null;
            ////Add formName
            //childImage.FormName = GetFromName(childImage.FormId, childImage.CustomerId);

            ////add categoryName
            //childImage.CategoryName = GetCategoryName(childImage.CategoryId);

			string storeTableName = childImage.StoreTableName;
			string jobTable = string.Empty;
	  

			if (childImage.OperatorEntryResults.Count(t=>t.Rank == EnumRank.A)>0)
			{
				var jobid = childImage.OperatorEntryResults.Single(t => t.Rank == EnumRank.A).JobId;

				jobTable = storeTableName.Replace("store", "abjobmana");
				childImage.OperatorEntryResults.Single(t => t.Rank == EnumRank.A).OperatorId = GetABopId(
					jobTable, childImage.CustomerId,
					childImage.OrderId,
					jobid, "01");
			}

			if (childImage.OperatorEntryResults.Count(t => t.Rank == EnumRank.B) > 0)
			{
				var jobid = childImage.OperatorEntryResults.Single(t => t.Rank == EnumRank.B).JobId;

				jobTable = storeTableName.Replace("store", "abjobmana");
				childImage.OperatorEntryResults.Single(t => t.Rank == EnumRank.B).OperatorId = GetABopId(
					jobTable, childImage.CustomerId,
					childImage.OrderId,
					jobid, "02");
			}


			if (childImage.OperatorEntryResults.Count(t => t.Rank == EnumRank.C) > 0)
			{
				var jobid = childImage.OperatorEntryResults.Single(t => t.Rank == EnumRank.C).JobId;

				jobTable = storeTableName.Replace("store", "cjobmana");
				childImage.OperatorEntryResults.Single(t => t.Rank == EnumRank.C).OperatorId = GetCopId(
					jobTable, childImage.CustomerId,
					childImage.OrderId,
					jobid);
			}

			if (childImage.OperatorEntryResults.Count(t => t.Rank == EnumRank.D) > 0)
			{
				var jobid = childImage.OperatorEntryResults.Single(t => t.Rank == EnumRank.D).JobId;

				jobTable = storeTableName.Replace("store", "djobmana");
				childImage.OperatorEntryResults.Single(t => t.Rank == EnumRank.D).OperatorId = GetDopId(
					jobTable, childImage.CustomerId,
					childImage.OrderId,
					jobid);
			}


			if (childImage.OperatorEntryResults.Count(t => t.Rank == EnumRank.E) > 0)
			{
				var jobid = childImage.OperatorEntryResults.Single(t => t.Rank == EnumRank.E).JobId;

				jobTable = storeTableName.Replace("store", "ejobmana");
				childImage.OperatorEntryResults.Single(t => t.Rank == EnumRank.E).OperatorId = GetEopId(
					jobTable, childImage.CustomerId,
					childImage.OrderId,
					jobid);
			}

			return childImage;
		}
		 

		public  string GetFromName(string formId,string customerId)
		{
			string sql = " SELECT f_form_Name [FormName] FROM t_im_form_mas   WITH (NOLOCK) WHERE f_form_id = @formId AND f_form_customer_id = @customerId";

			SqlParameter[] sqlParameters = 
			{ 
				new SqlParameter("@formId", formId),
				new SqlParameter("@customerId", customerId) 
			};

			string formName = string.Empty;
			try
			{
				formName = SqlHelper.ExecuteScalar(ConnectionString, System.Data.CommandType.Text, sql, sqlParameters).ToString();

			}
			catch (Exception ex)
			{

				Log.Console.Error("DataSource.GetFromName", ex);

			}


			return formName;
		}

		public string GetCategoryName(string categoryId)
		{
			string sql = "SELECT f_category_Name [CategoryName] FROM t_im_category_mas  WITH (NOLOCK) WHERE f_category_id = @categoryId";

			SqlParameter[] sqlParameters = 
			{ 
				new SqlParameter("@categoryId", categoryId) 
			};

			string categoryName = string.Empty;
			try
			{
				categoryName = SqlHelper.ExecuteScalar(ConnectionString, System.Data.CommandType.Text, sql, sqlParameters).ToString();

			}
			catch (Exception ex)
			{

				Log.Console.Error("DataSource.GetCategoryName", ex);

			}

			return categoryName;
		}
	}
}
